package a_o2odata_deal.utils.zhibo_zf

import java.text.SimpleDateFormat
import java.util.Date

import com.alibaba.fastjson.{JSON, JSONObject}
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, SQLContext}
import org.apache.spark.{SparkConf, SparkContext}

/**
  * @ Auther: o2o-rd-0008
  * @ Date:   2020/1/2 15:30
  * @ Param:  ${PARAM}
  * @ Description: 
  */
object handle_zhubo {

  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setAppName(s"${this.getClass.getSimpleName}")
    conf.set("spark.debug.maxToStringFields", "500")
    //conf.setMaster("local[*]")
    conf.set("es.nodes", "192.168.1.157")
    conf.set("es.port", "9200")
    conf.set("cluster.name", "O2OElastic")
    conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    //conf.set("spark.sql.caseSensitive", "true")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    sc.setLogLevel("WARN")
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")

    import org.elasticsearch.spark._
    sqlContext.udf.register("tranTimeToString",tranTimeToString _)

    //拉取es数据2019_all_anchor/all_anchor_2019_$months
    for (months <- 1 to 1){
      val value: RDD[String] = sc.esJsonRDD(s"2020_all_anchor/all_anchor_2020_${months}").values
      sqlContext.read.json(value).drop("nick").createOrReplaceTempView("anchor_data")
      //打上地址，先与本月数据进行关联，关联不上的关联其他历史数据取一个最新的
      sqlContext.read.json("s3a://o2o-dataproces-group/panzonghao/zhibo_zf/backups/all_userid/*/*")
        .where(s"tranTimeToString(timeStamp)!='2020-${months}'").dropDuplicates("userId","timeStamp")
        .createOrReplaceTempView("all_userId")

      sqlContext.sql(
        s"""
           |select
           |*
           |from
           |(select
           |*,
           |row_number() over(partition by userId order by cast(timeStamp as long) desc) as rank
           |from all_userId)
           |where rank=1
         """.stripMargin).drop("rank").createOrReplaceTempView("other_userId")

      //本月的uesrId数据
      sqlContext.read.json("s3a://o2o-dataproces-group/panzonghao/zhibo_zf/backups/all_userid/*/*")
          .where(s"tranTimeToString(timeStamp)='2020-${months}'").dropDuplicates("userId")
        .createOrReplaceTempView("userId")


      //首先关联本月的数据
      val first_data = sqlContext.sql(
        s"""
           |select
           |t1.*,
           |t2.regional_ID,
           |t2.province,
           |t2.city,
           |t2.district,
           |'true' as is_selfLive
           |from anchor_data t1
           |inner join
           |userId t2
           |on t1.anchorId=t2.userId
         """.stripMargin)

      sqlContext.sql(
        s"""
           |select
           |t1.*
           |from anchor_data t1
           |left join
           |userId t2
           |on t1.anchorId=t2.userId
           |where t2.userId  is null
         """.stripMargin).createOrReplaceTempView("no_join")

      val second_data = sqlContext.sql(
        s"""
           |select
           |t1.*,
           |case when t2.userId is null then '-1' else t2.province end as province,
           |case when t2.userId is null then '-1' else t2.city end as city,
           |case when t2.userId is null then '-1' else t2.district end as district,
           |case when t2.userId is null then '-1' else t2.regional_ID end as regional_ID,
           |case when t2.userId is null then 'false' else 'true' end as is_selfLive
           |from no_join t1
           |left join
           |other_userId t2
           |on t1.anchorId=t2.userId
         """.stripMargin)

      first_data.createOrReplaceTempView("first_data")
      second_data.createOrReplaceTempView("second_data")

      //加载主播集合  增加主播头像和昵称
      val anchor = sqlContext.read.json(s"${a_aa_amainpackage.a_o2odata_deal.config.config.tmall_anchor_id_path}")
        .dropDuplicates("anchorId").toJSON.rdd.map(line=>{
        val nObject: JSONObject = JSON.parseObject(line)

        val nick = nObject.getOrDefault("nick","-1").toString
        val location = nObject.getOrDefault("location","-1").toString
        val headImg = nObject.getOrDefault("headImg","-1").toString

        nObject.put("nick",nick)
        nObject.put("location",location)
        nObject.put("headImg",headImg)
        nObject.toString
      })

      val anchor_mid = sqlContext.read.json(anchor)
      val anchor_id = handle_city_out(anchor_mid,sqlContext)


      anchor_id.createOrReplaceTempView("anchor_id")

      val first_data1 = sqlContext.sql(
        s"""
           |select
           |t1.*,
           |case when t2.anchorId is null then '-1' else t2.nick end as nick,
           |case when t2.anchorId is null then '-1' else t2.headImg end as image
           |from first_data t1
           |left join
           |anchor_id t2
           |on t1.anchorId=t2.anchorId
         """.stripMargin)

      val second_data1 = sqlContext.sql(
        s"""
           |select
           |t1.*,
           |case when t2.anchorId is null then '-1' else t2.nick end as nick,
           |case when t2.anchorId is null then '-1' else t2.headImg end as image
           |from second_data t1
           |left join
           |anchor_id t2
           |on t1.anchorId=t2.anchorId
         """.stripMargin)


      val result = first_data1.selectExpr("anchorId","anchorFans","viewerCount","liveGoodCount","liveCount"
        ,"timeStamp","province","city","district","regional_ID","is_selfLive","nick","image")
        .union(second_data1.selectExpr("anchorId","anchorFans","viewerCount","liveGoodCount","liveCount"
          ,"timeStamp","province","city","district","regional_ID","is_selfLive","nick","image"))

      //将结果数据分为两部分  店铺主播且有地址的和达人主播和店铺主播没有地址的
      result.repartition(1).write.json(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/zhubo/2020/1/mid_v1")
      val result1 = sqlContext.read.json(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/zhubo/2020/1/mid_v1")

      val dp_zhubo = result1.where("is_selfLive = 'true' and province!='0'")
      val dr_zhubo = result1.where("is_selfLive = 'false' or (is_selfLive = 'true' and province='0')")

      //dp_zhubo.repartition(1).write.json(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/zhubo/dp_zhubo")
      //dr_zhubo.repartition(1).write.json(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/zhubo/dr_zhubo")

      //println("总条数:"+result.count())
      //println("dp_zhubo条数:"+dp_zhubo.count())
      //println("dr_zhubo条数:"+dr_zhubo.count())


      dp_zhubo.createOrReplaceTempView("dp_zhubo")
      dr_zhubo.createOrReplaceTempView("dr_zhubo")

      //添加达人主播的地址  先与机构地址关联  关联不上的再与采集的地址关联
      sqlContext.read.json(a_aa_amainpackage.a_o2odata_deal.config.config.ali_store_address_all_path)
          .dropDuplicates("shopId")
        .createOrReplaceTempView("ali_store_address_all")

      sqlContext.read.json(a_aa_amainpackage.a_o2odata_deal.config.config.ali_store_path)
          .dropDuplicates("affiliateId")
        .createOrReplaceTempView("ali_store")

      sqlContext.read.json(a_aa_amainpackage.a_o2odata_deal.config.config.ali_zb_path)
        .dropDuplicates("anchorId")
        .createOrReplaceTempView("ali_zb")

      sqlContext.sql(
        s"""
           |select distinct
           |t1.affiliateId,
           |t2.province,
           |t2.city,
           |t2.county as district,
           |t2.regional_ID
           |from ali_store t1
           |inner join
           |ali_store_address_all t2
           |on t1.affiliateId=t2.shopId
         """.stripMargin).createOrReplaceTempView("mid1")

      sqlContext.sql(
        s"""
           |select
           |t2.anchorId,
           |t1.province,
           |t1.city,
           |t1.district,
           |regional_ID
           |from mid1 t1
           |inner join
           |ali_zb t2
           |on t1.affiliateId=t2.affiliateId
         """.stripMargin).dropDuplicates("anchorId").createOrReplaceTempView("mid2")

      val dr_zhubo_v1 = sqlContext.sql(
        s"""
           |select
           |t1.*,
           |t2.province as provinces,
           |t2.city as citys,
           |t2.district as districts,
           |t2.regional_ID as regional_IDs
           |from dr_zhubo t1
           |inner join
           |mid2 t2
           |on t1.anchorId=t2.anchorId
         """.stripMargin).drop("province","city","district","regional_ID")
        .withColumnRenamed("provinces","province")
        .withColumnRenamed("citys","city")
        .withColumnRenamed("districts","district")
        .withColumnRenamed("regional_IDs","regional_ID")

      //dr_zhubo_v1.repartition(1).write.json(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/zhubo/dr_zhubo_v1")

      sqlContext.sql(
        s"""
           |select
           |t1.*
           |from dr_zhubo t1
           |left join
           |mid2 t2
           |on t1.anchorId=t2.anchorId
           |where t2.anchorId is null
         """.stripMargin).createOrReplaceTempView("mid3")

      val dr_zhubo_v2 = sqlContext.sql(
        s"""
           |select
           |t1.*,
           |case when t2.anchorId is null then t1.province else t2.province end as provinces,
           |case when t2.anchorId is null then t1.city else t2.city end as citys,
           |case when t2.anchorId is null then t1.district else t2.district end as districts,
           |case when t2.anchorId is null then t1.regional_ID else t2.regional_ID end as regional_IDs
           |from
           |mid3 t1
           |left join
           |anchor_id t2
           |on t1.anchorId=t2.anchorId
         """.stripMargin).drop("province","city","district","regional_ID")
        .withColumnRenamed("provinces","province")
        .withColumnRenamed("citys","city")
        .withColumnRenamed("districts","district")
        .withColumnRenamed("regional_IDs","regional_ID")


      //dr_zhubo_v2.repartition(1).write.json(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/zhubo/dr_zhubo_v2")



      val result_finally = dp_zhubo.selectExpr("anchorId","anchorFans","viewerCount","liveGoodCount","liveCount"
        ,"timeStamp","province","city","district","regional_ID","is_selfLive","nick","image")
        .union(dr_zhubo_v1.selectExpr("anchorId","anchorFans","viewerCount","liveGoodCount","liveCount"
          ,"timeStamp","province","city","district","regional_ID","is_selfLive","nick","image"))
        .union(dr_zhubo_v2.selectExpr("anchorId","anchorFans","viewerCount","liveGoodCount","liveCount"
          ,"timeStamp","province","city","district","regional_ID","is_selfLive","nick","image"))


      result_finally.dropDuplicates("anchorId").repartition(1).write.
        orc(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/2020/1/zhubo/")

    }




  }


  def handle_city_out(source_data:DataFrame,sqlContext:SQLContext):DataFrame={
    source_data.withColumnRenamed("location","chandi").createOrReplaceTempView("frame")

    sqlContext.sql(
      """
        |select
        |*,
        |case
        |when chandi rlike '咸阳'                or chandi rlike       '咸阳'                                     then  "咸阳市"
        |when chandi rlike '大理'                                                                                then  "大理白族自治州"
        |when chandi rlike '湘西'                                                                                then  "湘西土家族苗族自治州"
        |when chandi rlike '西双版纳'                                                                             then  "西双版纳傣族自治州"
        |when chandi rlike '神农架'                                                                               then  "省直辖县级行政区划"
        |when chandi rlike '宝兴'                                                                                then  "雅安市"
        |when chandi rlike '康定'                                                                                then  "甘孜藏族自治州"
        |when chandi rlike '黑水'                                                                                then  "阿坝藏族羌族自治州"
        |when chandi rlike '宁安'                                                                                then  "牡丹江市"
        |when chandi rlike '大新'    or chandi rlike '大新县'                                                      then  "崇左市"
        |when chandi rlike '当雄'    or chandi rlike '当雄县'                                                      then  "拉萨市"
        |when chandi rlike '凤凰'    or chandi rlike '凤凰古城'   or chandi rlike '凤凰县'                          then  "湘西土家族苗族自治州"
        |when chandi rlike '平凉'                or chandi rlike       '平凉'                                     then  "平凉市"
        |when chandi rlike '伊犁哈萨克'          or chandi rlike       '伊犁哈萨克'                                  then  "伊犁哈萨克自治州"
        |when chandi rlike '固原'                or chandi rlike       '固原'                                     then  "固原市"
        |when chandi rlike '巴音郭楞蒙古'        or chandi rlike       '巴音郭楞蒙古'                                 then  "巴音郭楞蒙古自治州"
        |when chandi rlike '西安'                or chandi rlike       '西安'                                     then  "西安市"
        |when chandi rlike '渭南'                or chandi rlike       '渭南'                                     then  "渭南市"
        |when chandi rlike '喀什地'              or chandi rlike       '喀什地'                                    then  "喀什地区"
        |when chandi rlike '石嘴山'              or chandi rlike       '石嘴山'                                    then  "石嘴山市"
        |when chandi rlike '海东'                or chandi rlike       '海东'                                     then  "海东市"
        |when chandi rlike '吴忠'                or chandi rlike       '吴忠'                                     then  "吴忠市"
        |when chandi rlike '榆林'                or chandi rlike       '榆林'                                     then  "榆林市"
        |when chandi rlike '阿克苏地'            or chandi rlike       '阿克苏地'                                   then  "阿克苏地区"
        |when chandi rlike '武威'                or chandi rlike       '武威'                                     then  "武威市"
        |when chandi rlike '汉中'                or chandi rlike       '汉中'                                     then  "汉中市"
        |when chandi rlike '金昌'                or chandi rlike       '金昌'                                     then  "金昌市"
        |when chandi rlike '临夏回族'            or chandi rlike       '临夏回族'                                   then  "临夏回族自治州"
        |when chandi rlike '果洛藏族'            or chandi rlike       '果洛藏族'                                   then  "果洛藏族自治州"
        |when chandi rlike '哈密'                or chandi rlike       '哈密'                                     then  "哈密市"
        |when chandi rlike '白银'                or chandi rlike       '白银'                                     then  "白银市"
        |when chandi rlike '阿勒泰地'            or chandi rlike       '阿勒泰地'                                   then  "阿勒泰地区"
        |when chandi rlike '陇南'                or chandi rlike       '陇南'                                     then  "陇南市"
        |when chandi rlike '宝鸡'                or chandi rlike       '宝鸡'                                     then  "宝鸡市"
        |when chandi rlike '海西蒙古族藏族'      or chandi rlike       '海西蒙古族藏族'                                then  "海西蒙古族藏族自治州"
        |when chandi rlike '庆阳'                or chandi rlike       '庆阳'                                     then  "庆阳市"
        |when chandi rlike '安康'                or chandi rlike       '安康'                                     then  "安康市"
        |when chandi rlike '兰州'                or chandi rlike       '兰州'                                     then  "兰州市"
        |when chandi rlike '延安'                or chandi rlike       '延安'                                     then  "延安市"
        |when chandi rlike '昌吉回族'            or chandi rlike       '昌吉回族'                                   then  "昌吉回族自治州"
        |when chandi rlike '商洛'                or chandi rlike       '商洛'                                     then  "商洛市"
        |when chandi rlike '银川'                or chandi rlike       '银川'                                     then  "银川市"
        |when chandi rlike '黄南藏族'            or chandi rlike       '黄南藏族'                                   then  "黄南藏族自治州"
        |when chandi rlike '酒泉'                or chandi rlike       '酒泉'                                     then  "酒泉市"
        |when chandi rlike '张掖'                or chandi rlike       '张掖'                                     then  "张掖市"
        |when chandi rlike '定西'                or chandi rlike       '定西'                                     then  "定西市"
        |when chandi rlike '塔城地'              or chandi rlike       '塔城地'                                    then  "塔城地区"
        |when chandi rlike '吐鲁番'              or chandi rlike       '吐鲁番'                                    then  "吐鲁番市"
        |when chandi rlike '嘉峪关'              or chandi rlike       '嘉峪关'                                    then  "嘉峪关市"
        |when chandi rlike '海北藏族'            or chandi rlike       '海北藏族'                                   then  "海北藏族自治州"
        |when chandi rlike '乌鲁木齐'            or chandi rlike       '乌鲁木齐'                                   then  "乌鲁木齐市"
        |when chandi rlike '克拉玛依'            or chandi rlike       '克拉玛依'                                   then  "克拉玛依市"
        |when chandi rlike '玉树藏族'            or chandi rlike       '玉树藏族'                                   then  "玉树藏族自治州"
        |when chandi rlike '天水'                or chandi rlike       '天水'                                     then  "天水市"
        |when chandi rlike '和田地'              or chandi rlike       '和田地'                                    then  "和田地区"
        |when chandi rlike '西宁'                or chandi rlike       '西宁'                                     then  "西宁市"
        |when chandi rlike '中卫'                or chandi rlike       '中卫'                                     then  "中卫市"
        |when chandi rlike '海南藏族'            or chandi rlike       '海南藏族'                                   then  "海南藏族自治州"
        |when chandi rlike '克孜勒苏柯尔克孜'    or chandi rlike       '克孜勒苏柯尔克孜'                               then  "克孜勒苏柯尔克孜自治州"
        |when chandi rlike '博尔塔拉蒙古'        or chandi rlike       '博尔塔拉蒙古'                                 then  "博尔塔拉蒙古自治州"
        |when chandi rlike '铜川'                or chandi rlike       '铜川'                                     then  "铜川市"
        |when chandi rlike '甘南藏族'            or chandi rlike       '甘南藏族'                                   then  "甘南藏族自治州"
        |when chandi rlike '松原'                or chandi rlike       '松原'                                     then  "松原市"
        |when chandi rlike '牡丹江'              or chandi rlike       '牡丹江'                                    then  "牡丹江市"
        |when chandi rlike '盘锦'                or chandi rlike       '盘锦'                                     then  "盘锦市"
        |when chandi rlike '伊春'                or chandi rlike       '伊春'                                     then  "伊春市"
        |when chandi rlike '通化'                or chandi rlike       '通化'                                     then  "通化市"
        |when chandi rlike '哈尔滨'              or chandi rlike       '哈尔滨'                                    then  "哈尔滨市"
        |when chandi rlike '营口'                or chandi rlike       '营口'                                     then  "营口市"
        |when chandi rlike '辽源'                or chandi rlike       '辽源'                                     then  "辽源市"
        |when chandi rlike '延边朝鲜族'          or chandi rlike       '延边朝鲜族'                                  then  "延边朝鲜族自治州"
        |when chandi rlike '白山'                or chandi rlike       '白山'                                     then  "白山市"
        |when chandi rlike '黑河'                or chandi rlike       '黑河'                                     then  "黑河市"
        |when chandi rlike '大连'                or chandi rlike       '大连'                                     then  "大连市"
        |when chandi rlike '双鸭山'              or chandi rlike       '双鸭山'                                    then  "双鸭山市"
        |when chandi rlike '大庆'                or chandi rlike       '大庆'                                     then  "大庆市"
        |when chandi rlike '沈阳'                or chandi rlike       '沈阳'                                     then  "沈阳市"
        |when chandi rlike '吉林'                or chandi rlike       '吉林'                                     then  "吉林市"
        |when chandi rlike '鸡西'                or chandi rlike       '鸡西'                                     then  "鸡西市"
        |when chandi rlike '辽阳'                or chandi rlike       '辽阳'                                     then  "辽阳市"
        |when chandi rlike '大兴安岭'            or chandi rlike       '大兴安岭'                                   then  "大兴安岭地区"
        |when chandi rlike '鹤岗'                or chandi rlike       '鹤岗'                                     then  "鹤岗市"
        |when chandi rlike '齐齐哈尔'            or chandi rlike       '齐齐哈尔'                                   then  "齐齐哈尔市"
        |when chandi rlike '抚顺'                or chandi rlike       '抚顺'                                     then  "抚顺市"
        |when chandi rlike '阜新'                or chandi rlike       '阜新'                                     then  "阜新市"
        |when chandi rlike '鞍山'                or chandi rlike       '鞍山'                                     then  "鞍山市"
        |when chandi rlike '绥化'                or chandi rlike       '绥化'                                     then  "绥化市"
        |when chandi rlike '葫芦岛'              or chandi rlike       '葫芦岛'                                    then  "葫芦岛市"
        |when chandi rlike '本溪'                or chandi rlike       '本溪'                                     then  "本溪市"
        |when chandi rlike '朝阳'                or chandi rlike       '朝阳'                                     then  "朝阳市"
        |when chandi rlike '七台河'              or chandi rlike       '七台河'                                    then  "七台河市"
        |when chandi rlike '长春'                or chandi rlike       '长春'                                     then  "长春市"
        |when chandi rlike '佳木斯'              or chandi rlike       '佳木斯'                                    then  "佳木斯市"
        |when chandi rlike '四平'                or chandi rlike       '四平'                                     then  "四平市"
        |when chandi rlike '铁岭'                or chandi rlike       '铁岭'                                     then  "铁岭市"
        |when chandi rlike '丹东'                or chandi rlike       '丹东'                                     then  "丹东市"
        |when chandi rlike '白城'                or chandi rlike       '白城'                                     then  "白城市"
        |when chandi rlike '锦州'                or chandi rlike       '锦州'                                     then  "锦州市"
        |when chandi rlike '赣州'                or chandi rlike       '赣州'                                     then  "赣州市"
        |when chandi rlike '上饶'                or chandi rlike       '上饶'                                     then  "上饶市"
        |when chandi rlike '淮北'                or chandi rlike       '淮北'                                     then  "淮北市"
        |when chandi rlike '南昌'                or chandi rlike       '南昌'                                     then  "南昌市"
        |when chandi rlike '福州'                or chandi rlike       '福州'                                     then  "福州市"
        |when chandi rlike '南平'                or chandi rlike       '南平'                                     then  "南平市"
        |when chandi rlike '合肥'                or chandi rlike       '合肥'                                     then  "合肥市"
        |when chandi rlike '徐州'                or chandi rlike       '徐州'                                     then  "徐州市"
        |when chandi rlike '烟台'                or chandi rlike       '烟台'                                     then  "烟台市"
        |when chandi rlike '金华'                or chandi rlike       '金华'                                     then  "金华市"
        |when chandi rlike '衢州'                or chandi rlike       '衢州'                                     then  "衢州市"
        |when chandi rlike '滨州'                or chandi rlike       '滨州'                                     then  "滨州市"
        |when chandi rlike '泰州'                or chandi rlike       '泰州'                                     then  "泰州市"
        |when chandi rlike '芜湖'                or chandi rlike       '芜湖'                                     then  "芜湖市"
        |when chandi rlike '济宁'                or chandi rlike       '济宁'                                     then  "济宁市"
        |when chandi rlike '枣庄'                or chandi rlike       '枣庄'                                     then  "枣庄市"
        |when chandi rlike '南通'                or chandi rlike       '南通'                                     then  "南通市"
        |when chandi rlike '泉州'                or chandi rlike       '泉州'                                     then  "泉州市"
        |when chandi rlike '滁州'                or chandi rlike       '滁州'                                     then  "滁州市"
        |when chandi rlike '济南'                or chandi rlike       '济南'                                     then  "济南市"
        |when chandi rlike '连云港'              or chandi rlike       '连云港'                                    then  "连云港市"
        |when chandi rlike '潍坊'                or chandi rlike       '潍坊'                                     then  "潍坊市"
        |when chandi rlike '扬州'                or chandi rlike       '扬州'                                     then  "扬州市"
        |when chandi rlike '铜陵'                or chandi rlike       '铜陵'                                     then  "铜陵市"
        |when chandi rlike '安庆'                or chandi rlike       '安庆'                                     then  "安庆市"
        |when chandi rlike '绍兴'                or chandi rlike       '绍兴'                                     then  "绍兴市"
        |when chandi rlike '青岛'                or chandi rlike       '青岛'                                     then  "青岛市"
        |when chandi rlike '菏泽'                or chandi rlike       '菏泽'                                     then  "菏泽市"
        |when chandi rlike '南京'                or chandi rlike       '南京'                                     then  "南京市"
        |when chandi rlike '苏州'                or chandi rlike       '苏州'    or chandi rlike       '太仓'      then  "苏州市"
        |when chandi rlike '丽水'                or chandi rlike       '丽水'                                     then  "丽水市"
        |when chandi rlike '湖州'                or chandi rlike       '湖州'                                     then  "湖州市"
        |when chandi rlike '长宁'                or chandi rlike       '长宁'                                     then  "长宁区"
        |when chandi rlike '静安'                or chandi rlike       '静安'                                     then  "静安区"
        |when chandi rlike '三明'                or chandi rlike       '三明'                                     then  "三明市"
        |when chandi rlike '威海'                or chandi rlike       '威海'                                     then  "威海市"
        |when chandi rlike '杭州'                or chandi rlike       '杭州'                                     then  "杭州市"
        |when chandi rlike '九江'                or chandi rlike       '九江'                                     then  "九江市"
        |when chandi rlike '淄博'                or chandi rlike       '淄博'                                     then  "淄博市"
        |when chandi rlike '宁波'                or chandi rlike       '宁波'                                     then  "宁波市"
        |when chandi rlike '吉安'                or chandi rlike       '吉安'                                     then  "吉安市"
        |when chandi rlike '淮安'                or chandi rlike       '淮安'                                     then  "淮安市"
        |when chandi rlike '台州'                or chandi rlike       '台州'                                     then  "台州市"
        |when chandi rlike '东营'                or chandi rlike       '东营'                                     then  "东营市"
        |when chandi rlike '舟山'                or chandi rlike       '舟山'                                     then  "舟山市"
        |when chandi rlike '漳州'                or chandi rlike       '漳州'                                     then  "漳州市"
        |when chandi rlike '宁德'  or   chandi rlike '福安市'                       or chandi rlike       '宁德'  or   chandi rlike '福安市'                                 then  "宁德市"
        |when chandi rlike '景德镇'              or chandi rlike       '景德镇'                                    then  "景德镇市"
        |when chandi rlike '抚州'                or chandi rlike       '抚州'                                     then  "抚州市"
        |when chandi rlike '莆田'                or chandi rlike       '莆田'                                     then  "莆田市"
        |when chandi rlike '常州'                or chandi rlike       '常州'                                     then  "常州市"
        |when chandi rlike '宿迁'                or chandi rlike       '宿迁'                                     then  "宿迁市"
        |when chandi rlike '镇江'                or chandi rlike       '镇江'                                     then  "镇江市"
        |when chandi rlike '宿州'                or chandi rlike       '宿州'                                     then  "宿州市"
        |when chandi rlike '宿州'                or chandi rlike       '宿州'                                     then  "宿州市"
        |when chandi rlike '普陀'                or chandi rlike       '普陀'                                     then  "普陀区"
        |when chandi rlike '龙岩'                or chandi rlike       '龙岩'                                     then  "龙岩市"
        |when chandi rlike '聊城'                or chandi rlike       '聊城'                                     then  "聊城市"
        |when chandi rlike '无锡'                or chandi rlike       '无锡'                                     then  "无锡市"
        |when chandi rlike '六安'                or chandi rlike       '六安'                                     then  "六安市"
        |when chandi rlike '池州'                or chandi rlike       '池州'                                     then  "池州市"
        |when chandi rlike '盐城'                or chandi rlike       '盐城'                                     then  "盐城市"
        |when chandi rlike '德州'                or chandi rlike       '德州'                                     then  "德州市"
        |when chandi rlike '亳州'                or chandi rlike       '亳州'                                     then  "亳州市"
        |when chandi rlike '新余'                or chandi rlike       '新余'                                     then  "新余市"
        |when chandi rlike '虹口'                or chandi rlike       '虹口'                                     then  "虹口区"
        |when chandi rlike '萍乡'                or chandi rlike       '萍乡'                                     then  "萍乡市"
        |when chandi rlike '泰安'                or chandi rlike       '泰安'                                     then  "泰安市"
        |when chandi rlike '闵行'                or chandi rlike       '闵行'                                     then  "闵行区"
        |when chandi rlike '日照'                or chandi rlike       '日照'                                     then  "日照市"
        |when chandi rlike '淮南'                or chandi rlike       '淮南'                                     then  "淮南市"
        |when chandi rlike '宣城'                or chandi rlike       '宣城'                                     then  "宣城市"
        |when chandi rlike '崇明'                or chandi rlike       '崇明'                                     then  "崇明区"
        |when chandi rlike '厦门'                or chandi rlike       '厦门'                                     then  "厦门市"
        |when chandi rlike '温州'                or chandi rlike       '温州'                                     then  "温州市"
        |when chandi rlike '马鞍山'              or chandi rlike       '马鞍山'                                    then  "马鞍山市"
        |when chandi rlike '嘉兴'                or chandi rlike       '嘉兴'                                     then  "嘉兴市"
        |when chandi rlike '蚌埠'                or chandi rlike       '蚌埠'                                     then  "蚌埠市"
        |when chandi rlike '黄山'                or chandi rlike       '黄山'                                     then  "黄山市"
        |when chandi rlike '阜阳'                or chandi rlike       '阜阳'                                     then  "阜阳市"
        |when chandi rlike '临沂'                or chandi rlike       '临沂'                                     then  "临沂市"
        |when chandi rlike '鹰潭'                or chandi rlike       '鹰潭'                                     then  "鹰潭市"
        |when chandi rlike '宜春'                or chandi rlike       '宜春'                                     then  "宜春市"
        |when chandi rlike '松江'                or chandi rlike       '松江'                                     then  "松江区"
        |when chandi rlike '浦东新'              or chandi rlike       '浦东新'                                    then  "浦东新区"
        |when chandi rlike '黄浦'                or chandi rlike       '黄浦'                                     then  "黄浦区"
        |when chandi rlike '奉贤'                or chandi rlike       '奉贤'                                     then  "奉贤区"
        |when chandi rlike '宝山'                or chandi rlike       '宝山'                                     then  "宝山区"
        |when chandi rlike '金山'                or chandi rlike       '金山'                                     then  "金山区"
        |when chandi rlike '嘉定'                or chandi rlike       '嘉定'                                     then  "嘉定区"
        |when chandi rlike '杨浦'                or chandi rlike       '杨浦'                                     then  "杨浦区"
        |when chandi rlike '徐汇'                or chandi rlike       '徐汇'                                     then  "徐汇区"
        |when chandi rlike '上海'                or chandi rlike       '上海'                                     then  "上海市"
        |when chandi rlike '青浦'                or chandi rlike       '青浦'                                     then  "青浦区"
        |when chandi rlike '桂林'                or chandi rlike       '桂林'                                     then  "桂林市"
        |when chandi rlike '玉林'                or chandi rlike       '玉林'                                     then  "玉林市"
        |when chandi rlike '柳州'                or chandi rlike       '柳州'                                     then  "柳州市"
        |when chandi rlike '河池'                or chandi rlike       '河池'                                     then  "河池市"
        |when chandi rlike '贺州'                or chandi rlike       '贺州'                                     then  "贺州市"
        |when chandi rlike '来宾'                or chandi rlike       '来宾'                                     then  "来宾市"
        |when chandi rlike '中山'                or chandi rlike       '中山'                                     then  "中山市"
        |when chandi rlike '潮州'                or chandi rlike       '潮州'                                     then  "潮州市"
        |when chandi rlike '南宁'                or chandi rlike       '南宁'                                     then  "南宁市"
        |when chandi rlike '贵港'                or chandi rlike       '贵港'                                     then  "贵港市"
        |when chandi rlike '清远'                or chandi rlike       '清远'                                     then  "清远市"
        |when chandi rlike '湛江'                or chandi rlike       '湛江'                                     then  "湛江市"
        |when chandi rlike '梅州'                or chandi rlike       '梅州'                                     then  "梅州市"
        |when chandi rlike '钦州'                or chandi rlike       '钦州'                                     then  "钦州市"
        |when chandi rlike '北海'                or chandi rlike       '北海'                                     then  "北海市"
        |when chandi rlike '百色'                or chandi rlike       '百色'                                     then  "百色市"
        |when chandi rlike '汕尾'                or chandi rlike       '汕尾'                                     then  "汕尾市"
        |when chandi rlike '东莞'                or chandi rlike       '东莞'                                     then  "东莞市"
        |when chandi rlike '崇左'                or chandi rlike       '崇左'                                     then  "崇左市"
        |when chandi rlike '阳江'                or chandi rlike       '阳江'                                     then  "阳江市"
        |when chandi rlike '珠海'                or chandi rlike       '珠海'                                     then  "珠海市"
        |when chandi rlike '韶关'                or chandi rlike       '韶关'                                     then  "韶关市"
        |when chandi rlike '佛山'                or chandi rlike       '佛山'                                     then  "佛山市"
        |when chandi rlike '河源'                or chandi rlike       '河源'                                     then  "河源市"
        |when chandi rlike '三亚'                or chandi rlike       '三亚'                                     then  "三亚市"
        |when chandi rlike '云浮'                or chandi rlike       '云浮'                                     then  "云浮市"
        |when chandi rlike '肇庆'                or chandi rlike       '肇庆'                                     then  "肇庆市"
        |when chandi rlike '防城港'              or chandi rlike       '防城港'                                    then  "防城港市"
        |when chandi rlike '梧州'                or chandi rlike       '梧州'                                     then  "梧州市"
        |when chandi rlike '深圳'                or chandi rlike       '深圳'                                     then  "深圳市"
        |when chandi rlike '江门'                or chandi rlike       '江门'                                     then  "江门市"
        |when chandi rlike '广州'                or chandi rlike       '广州'                                     then  "广州市"
        |when chandi rlike '茂名'                or chandi rlike       '茂名'                                     then  "茂名市"
        |when chandi rlike '揭阳'                or chandi rlike       '揭阳'                                     then  "揭阳市"
        |when chandi rlike '海口'                or chandi rlike       '海口'                                     then  "海口市"
        |when chandi rlike '汕头'                or chandi rlike       '汕头'                                     then  "汕头市"
        |when chandi rlike '惠州'                or chandi rlike       '惠州'                                     then  "惠州市"
        |when chandi rlike '儋州'                or chandi rlike       '儋州'                                     then  "儋州市"
        |when chandi rlike '三沙'                or chandi rlike       '三沙'                                     then  "三沙市"
        |when chandi rlike '丽江'                or chandi rlike       '丽江'                                     then  "丽江市"
        |when chandi rlike '唐山'                or chandi rlike       '唐山'                                     then  "唐山市"
        |when chandi rlike '大理白族'            or chandi rlike       '大理白族'                                   then  "大理白族自治州"
        |when chandi rlike '红河哈尼族彝族'      or chandi rlike       '红河哈尼族彝族'                                then  "红河哈尼族彝族自治州"
        |when chandi rlike '乐山'                or chandi rlike       '乐山'                                     then  "乐山市"
        |when chandi rlike '昭通'                or chandi rlike       '昭通'                                     then  "昭通市"
        |when chandi rlike '晋中'                or chandi rlike       '晋中'                                     then  "晋中市"
        |when chandi rlike '西双版纳傣族'        or chandi rlike       '西双版纳傣族'                                 then  "西双版纳傣族自治州"
        |when chandi rlike '日喀则'              or chandi rlike       '日喀则'                                    then  "日喀则市"
        |when chandi rlike '迪庆藏族'            or chandi rlike       '迪庆藏族'                                   then  "迪庆藏族自治州"
        |when chandi rlike '安顺'                or chandi rlike       '安顺'                                     then  "安顺市"
        |when chandi rlike '昌都'                or chandi rlike       '昌都'                                     then  "昌都市"
        |when chandi rlike '邢台'                or chandi rlike       '邢台'                                     then  "邢台市"
        |when chandi rlike '长治'                or chandi rlike       '长治'                                     then  "长治市"
        |when chandi rlike '沧州'                or chandi rlike       '沧州'                                     then  "沧州市"
        |when chandi rlike '承德'                or chandi rlike       '承德'                                     then  "承德市"
        |when chandi rlike '宜宾'                or chandi rlike       '宜宾'                                     then  "宜宾市"
        |when chandi rlike '运城'                or chandi rlike       '运城'                                     then  "运城市"
        |when chandi rlike '巴中'                or chandi rlike       '巴中'                                     then  "巴中市"
        |when chandi rlike '包头'                or chandi rlike       '包头'                                     then  "包头市"
        |when chandi rlike '保定'                or chandi rlike       '保定'                                     then  "保定市"
        |when chandi rlike '石家庄'              or chandi rlike       '石家庄'                                    then  "石家庄市"
        |when chandi rlike '衡水'                or chandi rlike       '衡水'                                     then  "衡水市"
        |when chandi rlike '昆明'                or chandi rlike       '昆明'                                     then  "昆明市"
        |when chandi rlike '雅安'                or chandi rlike       '雅安'                                     then  "雅安市"
        |when chandi rlike '铜仁'                or chandi rlike       '铜仁'                                     then  "铜仁市"
        |when chandi rlike '乌兰察布'            or chandi rlike       '乌兰察布'                                   then  "乌兰察布市"
        |when chandi rlike '甘孜藏族'            or chandi rlike       '甘孜藏族'                                   then  "甘孜藏族自治州"
        |when chandi rlike '山南'                or chandi rlike       '山南'                                     then  "山南市"
        |when chandi rlike '临汾'                or chandi rlike       '临汾'                                     then  "临汾市"
        |when chandi rlike '保山'                or chandi rlike       '保山'                                     then  "保山市"
        |when chandi rlike '朔州'                or chandi rlike       '朔州'                                     then  "朔州市"
        |when chandi rlike '凉山彝族'            or chandi rlike       '凉山彝族'                                   then  "凉山彝族自治州"
        |when chandi rlike '泸州'                or chandi rlike       '泸州'                                     then  "泸州市"
        |when chandi rlike '成都'                or chandi rlike       '成都'                                     then  "成都市"
        |when chandi rlike '鄂尔多斯'            or chandi rlike       '鄂尔多斯'                                   then  "鄂尔多斯市"
        |when chandi rlike '那曲'                or chandi rlike       '那曲'                                     then  "那曲地区"
        |when chandi rlike '黔东南苗族侗族'      or chandi rlike       '黔东南苗族侗族'                                then  "黔东南苗族侗族自治州"
        |when chandi rlike '通辽'                or chandi rlike       '通辽'                                     then  "通辽市"
        |when chandi rlike '绵阳'                or chandi rlike       '绵阳'                                     then  "绵阳市"
        |when chandi rlike '德阳'                or chandi rlike       '德阳'                                     then  "德阳市"
        |when chandi rlike '璧山'                or chandi rlike       '璧山'                                     then  "璧山区"
        |when chandi rlike '邯郸'                or chandi rlike       '邯郸'                                     then  "邯郸市"
        |when chandi rlike '阿拉善盟'            or chandi rlike       '阿拉善盟'                                   then  "阿拉善盟"
        |when chandi rlike '东丽'                or chandi rlike       '东丽'                                     then  "东丽区"
        |when chandi rlike '太原'                or chandi rlike       '太原'                                     then  "太原市"
        |when chandi rlike '兴安盟'              or chandi rlike       '兴安盟'                                    then  "兴安盟"
        |when chandi rlike '秦皇岛'              or chandi rlike       '秦皇岛'                                    then  "秦皇岛市"
        |when chandi rlike '锡林郭勒盟'          or chandi rlike       '锡林郭勒盟'                                  then  "锡林郭勒盟"
        |when chandi rlike '呼和浩特'            or chandi rlike       '呼和浩特'                                   then  "呼和浩特市"
        |when chandi rlike '密云'                or chandi rlike       '密云'                                     then  "密云区"
        |when chandi rlike '拉萨'                or chandi rlike       '拉萨'                                     then  "拉萨市"
        |when chandi rlike '赤峰'                or chandi rlike       '赤峰'                                     then  "赤峰市"
        |when chandi rlike '阿里地'              or chandi rlike       '阿里地'                                    then  "阿里地区"
        |when chandi rlike '临沧'                or chandi rlike       '临沧'                                     then  "临沧市"
        |when chandi rlike '眉山'                or chandi rlike       '眉山'                                     then  "眉山市"
        |when chandi rlike '忻州'  or   chandi rlike '宁武县'                       or chandi rlike       '忻州'  or   chandi rlike '宁武县'                                  then  "忻州市"
        |when chandi rlike '黔南布依族苗族'      or chandi rlike       '黔南布依族苗族'                                then  "黔南布依族苗族自治州"
        |when chandi rlike '玉溪'                or chandi rlike       '玉溪'                                     then  "玉溪市"
        |when chandi rlike '沙坪坝'              or chandi rlike       '沙坪坝'                                    then  "沙坪坝区"
        |when chandi rlike '张家口'              or chandi rlike       '张家口'                                    then  "张家口市"
        |when chandi rlike '南充'                or chandi rlike       '南充'                                     then  "南充市"
        |when chandi rlike '巴彦淖尔'            or chandi rlike       '巴彦淖尔'                                   then  "巴彦淖尔市"
        |when chandi rlike '重庆'                or chandi rlike       '重庆'                                     then  "重庆市"
        |when chandi rlike '吕梁'                or chandi rlike       '吕梁'                                     then  "吕梁市"
        |when chandi rlike '黔西南布依族苗族'    or chandi rlike       '黔西南布依族苗族'                               then  "黔西南布依族苗族自治州"
        |when chandi rlike '阿坝藏族羌族'        or chandi rlike       '阿坝藏族羌族'                                 then  "阿坝藏族羌族自治州"
        |when chandi rlike '广元'                or chandi rlike       '广元'                                     then  "广元市"
        |when chandi rlike '呼伦贝尔'            or chandi rlike       '呼伦贝尔'                                   then  "呼伦贝尔市"
        |when chandi rlike '北碚'                or chandi rlike       '北碚'                                     then  "北碚区"
        |when chandi rlike '延庆'                or chandi rlike       '延庆'                                     then  "延庆区"
        |when chandi rlike '文山壮族苗族'        or chandi rlike       '文山壮族苗族'                                 then  "文山壮族苗族自治州"
        |when chandi rlike '涪陵'                or chandi rlike       '涪陵'                                     then  "涪陵区"
        |when chandi rlike '遵义'                or chandi rlike       '遵义'                                     then  "遵义市"
        |when chandi rlike '大同'                or chandi rlike       '大同'                                     then  "大同市"
        |when chandi rlike '资阳'                or chandi rlike       '资阳'                                     then  "资阳市"
        |when chandi rlike '昌平'                or chandi rlike       '昌平'                                     then  "昌平区"
        |when chandi rlike '普洱'   or chandi rlike '思茅区'                        or chandi rlike       '普洱'   or chandi rlike '思茅区'                                  then  "普洱市"
        |when chandi rlike '乌海'                or chandi rlike       '乌海'                                     then  "乌海市"
        |when chandi rlike '六盘水'              or chandi rlike       '六盘水'                                    then  "六盘水市"
        |when chandi rlike '蓟州'                or chandi rlike       '蓟州'                                     then  "蓟州区"
        |when chandi rlike '巫溪县'              or chandi rlike       '巫溪县'                                    then  "巫溪县"
        |when chandi rlike '阳泉'                or chandi rlike       '阳泉'                                     then  "阳泉市"
        |when chandi rlike '怒江傈僳族'          or chandi rlike       '怒江傈僳族'                                  then  "怒江傈僳族自治州"
        |when chandi rlike '合川'                or chandi rlike       '合川'                                     then  "合川区"
        |when chandi rlike '怀柔'                or chandi rlike       '怀柔'                                     then  "怀柔区"
        |when chandi rlike '林芝'                or chandi rlike       '林芝'                                     then  "林芝市"
        |when chandi rlike '楚雄彝族'            or chandi rlike       '楚雄彝族'                                   then  "楚雄彝族自治州"
        |when chandi rlike '遂宁'                or chandi rlike       '遂宁'                                     then  "遂宁市"
        |when chandi rlike '北京'                or chandi rlike       '北京'                                     then  "北京市"
        |when chandi rlike '渝北'                or chandi rlike       '渝北'                                     then  "渝北区"
        |when chandi rlike '垫江县'              or chandi rlike       '垫江县'                                    then  "垫江县"
        |when chandi rlike '曲靖'                or chandi rlike       '曲靖'                                     then  "曲靖市"
        |when chandi rlike '巴南'                or chandi rlike       '巴南'                                     then  "巴南区"
        |when chandi rlike '天津'                or chandi rlike       '天津'                                     then  "天津市"
        |when chandi rlike '顺义'                or chandi rlike       '顺义'                                     then  "顺义区"
        |when chandi rlike '江津'                or chandi rlike       '江津'                                     then  "江津区"
        |when chandi rlike '荣昌'                or chandi rlike       '荣昌'                                     then  "荣昌区"
        |when chandi rlike '平谷'                or chandi rlike       '平谷'                                     then  "平谷区"
        |when chandi rlike '大足'                or chandi rlike       '大足'                                     then  "大足区"
        |when chandi rlike '德宏傣族景颇族'      or chandi rlike       '德宏傣族景颇族'                                then  "德宏傣族景颇族自治州"
        |when chandi rlike '静海'                or chandi rlike       '静海'                                     then  "静海区"
        |when chandi rlike '武清'                or chandi rlike       '武清'                                     then  "武清区"
        |when chandi rlike '海淀'                or chandi rlike       '海淀'                                     then  "海淀区"
        |when chandi rlike '西青'                or chandi rlike       '西青'                                     then  "西青区"
        |when chandi rlike '北辰'                or chandi rlike       '北辰'                                     then  "北辰区"
        |when chandi rlike '武隆县'              or chandi rlike       '武隆县'                                    then  "武隆县"
        |when chandi rlike '南开'                or chandi rlike       '南开'                                     then  "南开区"
        |when chandi rlike '黔江'                or chandi rlike       '黔江'                                     then  "黔江区"
        |when chandi rlike '通州'                or chandi rlike       '通州'                                     then  "通州区"
        |when chandi rlike '大兴'                or chandi rlike       '大兴'                                     then  "大兴区"
        |when chandi rlike '南川'                or chandi rlike       '南川'                                     then  "南川区"
        |when chandi rlike '綦江'                or chandi rlike       '綦江'                                     then  "綦江区"
        |when chandi rlike '河西'                or chandi rlike       '河西'                                     then  "河西区"
        |when chandi rlike '秀山土家族苗族'      or chandi rlike       '秀山土家族苗族'                                then  "秀山土家族苗族自治县"
        |when chandi rlike '东城'                or chandi rlike       '东城'                                     then  "东城区"
        |when chandi rlike '廊坊'                or chandi rlike       '廊坊'                                     then  "廊坊市"
        |when chandi rlike '晋城'                or chandi rlike       '晋城'                                     then  "晋城市"
        |when chandi rlike '自贡'                or chandi rlike       '自贡'                                     then  "自贡市"
        |when chandi rlike '攀枝花'              or chandi rlike       '攀枝花'                                    then  "攀枝花市"
        |when chandi rlike '毕节'                or chandi rlike       '毕节'                                     then  "毕节市"
        |when chandi rlike '城口县'              or chandi rlike       '城口县'                                    then  "城口县"
        |when chandi rlike '潼南'                or chandi rlike       '潼南'                                     then  "潼南区"
        |when chandi rlike '达州'                or chandi rlike       '达州'                                     then  "达州市"
        |when chandi rlike '忠县'                or chandi rlike       '忠县'                                     then  "忠县"
        |when chandi rlike '九龙坡'              or chandi rlike       '九龙坡'                                    then  "九龙坡区"
        |when chandi rlike '门头沟'              or chandi rlike       '门头沟'                                    then  "门头沟区"
        |when chandi rlike '丰台'                or chandi rlike       '丰台'                                     then  "丰台区"
        |when chandi rlike '广安'                or chandi rlike       '广安'                                     then  "广安市"
        |when chandi rlike '滨海新'              or chandi rlike       '滨海新'                                    then  "滨海新区"
        |when chandi rlike '渝中'                or chandi rlike       '渝中'                                     then  "渝中区"
        |when chandi rlike '贵阳'                or chandi rlike       '贵阳'                                     then  "贵阳市"
        |when chandi rlike '房山'                or chandi rlike       '房山'                                     then  "房山区"
        |when chandi rlike '内江'                or chandi rlike       '内江'                                     then  "内江市"
        |when chandi rlike '丰都县'              or chandi rlike       '丰都县'                                    then  "丰都县"
        |when chandi rlike '津南'                or chandi rlike       '津南'                                     then  "津南区"
        |when chandi rlike '宁河'                or chandi rlike       '宁河'                                     then  "宁河区"
        |when chandi rlike '西城'                or chandi rlike       '西城'                                     then  "西城区"
        |when chandi rlike '大渡口'              or chandi rlike       '大渡口'                                    then  "大渡口区"
        |when chandi rlike '永川'                or chandi rlike       '永川'                                     then  "永川区"
        |when chandi rlike '河北'                or chandi rlike       '河北'                                     then  "河北区"
        |when chandi rlike '石柱土家族'          or chandi rlike       '石柱土家族'                                  then  "石柱土家族自治县"
        |when chandi rlike '彭水苗族土家族'      or chandi rlike       '彭水苗族土家族'                                then  "彭水苗族土家族自治县"
        |when chandi rlike '巫山县'              or chandi rlike       '巫山县'                                    then  "巫山县"
        |when chandi rlike '河东'                or chandi rlike       '河东'                                     then  "河东区"
        |when chandi rlike '宝坻'                or chandi rlike       '宝坻'                                     then  "宝坻区"
        |when chandi rlike '万州'                or chandi rlike       '万州'                                     then  "万州区"
        |when chandi rlike '石景山'              or chandi rlike       '石景山'                                    then  "石景山区"
        |when chandi rlike '云阳县'              or chandi rlike       '云阳县'                                    then  "云阳县"
        |when chandi rlike '铜梁'                or chandi rlike       '铜梁'                                     then  "铜梁区"
        |when chandi rlike '梁平县'              or chandi rlike       '梁平县'                                    then  "梁平县"
        |when chandi rlike '奉节县'              or chandi rlike       '奉节县'                                    then  "奉节县"
        |when chandi rlike '开州'                or chandi rlike       '开州'                                     then  "开州区"
        |when chandi rlike '南岸'                or chandi rlike       '南岸'                                     then  "南岸区"
        |when chandi rlike '酉阳土家族苗族'      or chandi rlike       '酉阳土家族苗族'                                then  "酉阳土家族苗族自治县"
        |when chandi rlike '江北'                or chandi rlike       '江北'                                     then  "江北区"
        |when chandi rlike '和平'                or chandi rlike       '和平'                                     then  "和平区"
        |when chandi rlike '红桥'                or chandi rlike       '红桥'                                     then  "红桥区"
        |when chandi rlike '朝阳'                or chandi rlike       '朝阳'                                     then  "朝阳区"
        |when chandi rlike '长寿'                or chandi rlike       '长寿'                                     then  "长寿区"
        |when chandi rlike '那曲'                or chandi rlike       '那曲'                                     then  "那曲市"
        |when chandi rlike '洛阳'                or chandi rlike       '洛阳'                                     then  "洛阳市"
        |when chandi rlike '漯河'                or chandi rlike       '漯河'                                     then  "漯河市"
        |when chandi rlike '襄阳'                or chandi rlike       '襄阳'                                     then  "襄阳市"
        |when chandi rlike '株洲'                or chandi rlike       '株洲'                                     then  "株洲市"
        |when chandi rlike '开封'                or chandi rlike       '开封'                                     then  "开封市"
        |when chandi rlike '郑州'                or chandi rlike       '郑州'                                     then  "郑州市"
        |when chandi rlike '邵阳'                or chandi rlike       '邵阳'                                     then  "邵阳市"
        |when chandi rlike '湘潭'                or chandi rlike       '湘潭'                                     then  "湘潭市"
        |when chandi rlike '平顶山'              or chandi rlike       '平顶山'                                    then  "平顶山市"
        |when chandi rlike '湘西土家族苗族'      or chandi rlike       '湘西土家族苗族'                                then  "湘西土家族苗族自治州"
        |when chandi rlike '焦作'                or chandi rlike       '焦作'                                     then  "焦作市"
        |when chandi rlike '濮阳'                or chandi rlike       '濮阳'                                     then  "濮阳市"
        |when chandi rlike '益阳'                or chandi rlike       '益阳'                                     then  "益阳市"
        |when chandi rlike '岳阳'                or chandi rlike       '岳阳'                                     then  "岳阳市"
        |when chandi rlike '新乡'                or chandi rlike       '新乡'                                     then  "新乡市"
        |when chandi rlike '十堰'                or chandi rlike       '十堰'                                     then  "十堰市"
        |when chandi rlike '永州'                or chandi rlike       '永州'                                     then  "永州市"
        |when chandi rlike '郴州'                or chandi rlike       '郴州'                                     then  "郴州市"
        |when chandi rlike '衡阳'                or chandi rlike       '衡阳'                                     then  "衡阳市"
        |when chandi rlike '孝感'                or chandi rlike       '孝感'                                     then  "孝感市"
        |when chandi rlike '许昌'                or chandi rlike       '许昌'                                     then  "许昌市"
        |when chandi rlike '恩施土家族苗族'      or chandi rlike       '恩施土家族苗族'                                then  "恩施土家族苗族自治州"
        |when chandi rlike '荆州'                or chandi rlike       '荆州'                                     then  "荆州市"
        |when chandi rlike '鹤壁'                or chandi rlike       '鹤壁'                                     then  "鹤壁市"
        |when chandi rlike '怀化'                or chandi rlike       '怀化'                                     then  "怀化市"
        |when chandi rlike '咸宁'                or chandi rlike       '咸宁'                                     then  "咸宁市"
        |when chandi rlike '武汉'                or chandi rlike       '武汉'                                     then  "武汉市"
        |when chandi rlike '荆门'                or chandi rlike       '荆门'                                     then  "荆门市"
        |when chandi rlike '长沙'                or chandi rlike       '长沙'                                     then  "长沙市"
        |when chandi rlike '周口'                or chandi rlike       '周口'                                     then  "周口市"
        |when chandi rlike '南阳'                or chandi rlike       '南阳'                                     then  "南阳市"
        |when chandi rlike '常德'                or chandi rlike       '常德'                                     then  "常德市"
        |when chandi rlike '安阳'                or chandi rlike       '安阳'                                     then  "安阳市"
        |when chandi rlike '黄石'                or chandi rlike       '黄石'                                     then  "黄石市"
        |when chandi rlike '宜昌'                or chandi rlike       '宜昌'                                     then  "宜昌市"
        |when chandi rlike '张家界'              or chandi rlike       '张家界'                                    then  "张家界市"
        |when chandi rlike '随州'                or chandi rlike       '随州'                                     then  "随州市"
        |when chandi rlike '驻马店'              or chandi rlike       '驻马店'                                    then  "驻马店市"
        |when chandi rlike '商丘'                or chandi rlike       '商丘'                                     then  "商丘市"
        |when chandi rlike '娄底'                or chandi rlike       '娄底'                                     then  "娄底市"
        |when chandi rlike '三门峡'              or chandi rlike       '三门峡'                                    then  "三门峡市"
        |when chandi rlike '黄冈'                or chandi rlike       '黄冈'                                     then  "黄冈市"
        |when chandi rlike '鄂州'                or chandi rlike       '鄂州'                                     then  "鄂州市"
        |when chandi rlike '信阳'                or chandi rlike       '信阳'                                     then  "信阳市"
        |when chandi rlike '都匀'                or chandi rlike       '都匀'                                     then  "都匀市"
        |else chandi end as city_tmp
        |from frame
      """.stripMargin).drop("chandi").createOrReplaceTempView("data")
    //加载标准地址表
    val address = sqlContext.read.option("header","true")
      .csv("s3a://o2o-dimension-table/address_table/address_table_2019/address_mapping_standard/2019_9/")
    address.createOrReplaceTempView("address")

    val result = sqlContext.sql(
      s"""
         |select
         |t1.*,
         |case
         |when t2.city is null then '-1'
         |else t2.province
         |end as province,
         |case
         |when t2.city is null then '-1'
         |else t2.city
         |end as city,
         |case
         |when t2.city is null then '-1'
         |else t2.uniqueid
         |end as regional_ID,
         |'-1' as district
         |from data t1
         |left join
         |(select distinct province,city,uniqueid from address where city!='0' and district='0') t2
         |on t1.city_tmp=t2.city
       """.stripMargin).drop("city_tmp").dropDuplicates("anchorId")



    result
  }


  def tranTimeToString(timestamp:String) :String={
    val tm = timestamp+"000"
    val fm = new SimpleDateFormat("yyyy-M")
    val time = fm.format(new Date(tm.toLong))
    time
  }
}




















